From 9816dcbe8e47d54aeb9fbac9bb84caeab37e53c1 Mon Sep 17 00:00:00 2001 From: freakolowsky Date: Mon, 31 Dec 2012 15:12:11 +0100 Subject: [PATCH] * Oracle Schema update to latest * fixed makeSelectOptions (bug 43533) Change-Id: Ib146a5073de18f2b20a22f34bd60222b230c9514 --- includes/db/DatabaseOracle.php | 26 ++++++++-- includes/installer/OracleUpdater.php | 7 ++- maintenance/oracle/archives/patch-fa_sha1.sql | 5 ++ .../oracle/archives/patch-job_attempts.sql | 4 ++ .../oracle/archives/patch-job_token.sql | 12 +++++ maintenance/oracle/archives/patch-sites.sql | 34 +++++++++++++ .../archives/patch-uploadstash-us_props.sql | 4 ++ maintenance/oracle/tables.sql | 48 ++++++++++++++++++- 8 files changed, 132 insertions(+), 8 deletions(-) create mode 100644 maintenance/oracle/archives/patch-fa_sha1.sql create mode 100644 maintenance/oracle/archives/patch-job_attempts.sql create mode 100644 maintenance/oracle/archives/patch-job_token.sql create mode 100644 maintenance/oracle/archives/patch-sites.sql create mode 100644 maintenance/oracle/archives/patch-uploadstash-us_props.sql diff --git a/includes/db/DatabaseOracle.php b/includes/db/DatabaseOracle.php index 2f9a05ce0e..7e3c0caf5c 100644 --- a/includes/db/DatabaseOracle.php +++ b/includes/db/DatabaseOracle.php @@ -1167,14 +1167,30 @@ class DatabaseOracle extends DatabaseBase { } if ( isset( $options['GROUP BY'] ) ) { - $preLimitTail .= " GROUP BY {$options['GROUP BY']}"; + $gb = is_array( $options['GROUP BY'] ) + ? implode( ',', $options['GROUP BY'] ) + : $options['GROUP BY']; + $preLimitTail .= " GROUP BY {$gb}"; } + + if ( isset( $options['HAVING'] ) ) { + $having = is_array( $options['HAVING'] ) + ? $this->makeList( $options['HAVING'], LIST_AND ) + : $options['HAVING']; + $preLimitTail .= " HAVING {$having}"; + } + if ( isset( $options['ORDER BY'] ) ) { - $preLimitTail .= " ORDER BY {$options['ORDER BY']}"; + $ob = is_array( $options['ORDER BY'] ) + ? implode( ',', $options['ORDER BY'] ) + : $options['ORDER BY']; + $preLimitTail .= " ORDER BY {$ob}"; + } + + if ( isset( $noKeyOptions['FOR UPDATE'] ) ) { + $postLimitTail .= ' FOR UPDATE'; } - # if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $tailOpts .= ' FOR UPDATE'; - # if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $tailOpts .= ' LOCK IN SHARE MODE'; if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) { $startOpts .= 'DISTINCT'; } @@ -1187,7 +1203,7 @@ class DatabaseOracle extends DatabaseBase { return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail ); } - + public function delete( $table, $conds, $fname = 'DatabaseOracle::delete' ) { if ( is_array($conds) ) { $conds = $this->wrapConditionsForWhere( $table, $conds ); diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 5523470e1f..86b52d6520 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -79,6 +79,11 @@ class OracleUpdater extends DatabaseUpdater { array( 'addField', 'page', 'page_content_model', 'patch-page-page_content_model.sql' ), array( 'dropField', 'site_stats', 'ss_admins', 'patch-ss_admins.sql' ), array( 'dropField', 'recentchanges', 'rc_moved_to_title', 'patch-rc_moved.sql' ), + array( 'addTable', 'sites', 'patch-sites.sql' ), + array( 'addField', 'filearchive', 'fa_sha1', 'patch-fa_sha1.sql' ), + array( 'addField', 'job', 'job_token', 'patch-job_token.sql' ), + array( 'addField', 'job', 'job_attempts', 'patch-job_attempts.sql' ), + array( 'addField', 'uploadstash', 'us_props', 'patch-uploadstash-us_props.sql' ), // KEEP THIS AT THE BOTTOM!! array( 'doRebuildDuplicateFunction' ), @@ -225,7 +230,7 @@ class OracleUpdater extends DatabaseUpdater { /** * Overload: because of the DDL_MODE tablename escaping is a bit dodgy */ - protected function purgeCache() { + public function purgeCache() { # We can't guarantee that the user will be able to use TRUNCATE, # but we know that DELETE is available to us $this->output( "Purging caches..." ); diff --git a/maintenance/oracle/archives/patch-fa_sha1.sql b/maintenance/oracle/archives/patch-fa_sha1.sql new file mode 100644 index 0000000000..70c9e60cb2 --- /dev/null +++ b/maintenance/oracle/archives/patch-fa_sha1.sql @@ -0,0 +1,5 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.filearchive ADD fa_sha1 VARCHAR2(32); +CREATE INDEX &mw_prefix.filearchive_i05 ON &mw_prefix.filearchive (fa_sha1); + diff --git a/maintenance/oracle/archives/patch-job_attempts.sql b/maintenance/oracle/archives/patch-job_attempts.sql new file mode 100644 index 0000000000..b05c877954 --- /dev/null +++ b/maintenance/oracle/archives/patch-job_attempts.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.job ADD job_attempts NUMBER DEFAULT 0 NOT NULL; +CREATE INDEX &mw_prefix.job_i05 ON &mw_prefix.job (job_attempts); diff --git a/maintenance/oracle/archives/patch-job_token.sql b/maintenance/oracle/archives/patch-job_token.sql new file mode 100644 index 0000000000..1a730e9539 --- /dev/null +++ b/maintenance/oracle/archives/patch-job_token.sql @@ -0,0 +1,12 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.job ADD ( + job_random NUMBER DEFAULT 0 NOT NULL, + job_token VARCHAR2(32), + job_token_timestamp TIMESTAMP(6) WITH TIME ZONE, + job_sha1 VARCHAR2(32) +); + +CREATE INDEX &mw_prefix.job_i03 ON &mw_prefix.job (job_sha1); +CREATE INDEX &mw_prefix.job_i04 ON &mw_prefix.job (job_cmd,job_token,job_random); + diff --git a/maintenance/oracle/archives/patch-sites.sql b/maintenance/oracle/archives/patch-sites.sql new file mode 100644 index 0000000000..868b210f78 --- /dev/null +++ b/maintenance/oracle/archives/patch-sites.sql @@ -0,0 +1,34 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE SEQUENCE sites_site_id_seq MINVALUE 0 START WITH 0; +CREATE TABLE &mw_prefix.sites ( + site_id NUMBER NOT NULL, + site_global_key VARCHAR2(32) NOT NULL, + site_type VARCHAR2(32) NOT NULL, + site_group VARCHAR2(32) NOT NULL, + site_source VARCHAR2(32) NOT NULL, + site_language VARCHAR2(32) NOT NULL, + site_protocol VARCHAR2(32) NOT NULL, + site_domain VARCHAR2(255) NOT NULL, + site_data BLOB NOT NULL, + site_forward NUMBER(1) NOT NULL, + site_config BLOB NOT NULL +); +ALTER TABLE &mw_prefix.sites ADD CONSTRAINT &mw_prefix.sites_pk PRIMARY KEY (site_id); +CREATE UNIQUE INDEX &mw_prefix.sites_u01 ON &mw_prefix.sites (site_global_key); +CREATE INDEX &mw_prefix.sites_i01 ON &mw_prefix.sites (site_type); +CREATE INDEX &mw_prefix.sites_i02 ON &mw_prefix.sites (site_group); +CREATE INDEX &mw_prefix.sites_i03 ON &mw_prefix.sites (site_source); +CREATE INDEX &mw_prefix.sites_i04 ON &mw_prefix.sites (site_language); +CREATE INDEX &mw_prefix.sites_i05 ON &mw_prefix.sites (site_protocol); +CREATE INDEX &mw_prefix.sites_i06 ON &mw_prefix.sites (site_domain); +CREATE INDEX &mw_prefix.sites_i07 ON &mw_prefix.sites (site_forward); + +CREATE TABLE &mw_prefix.site_identifiers ( + si_site NUMBER NOT NULL, + si_type VARCHAR2(32) NOT NULL, + si_key VARCHAR2(32) NOT NULL +); +CREATE UNIQUE INDEX &mw_prefix.site_identifiers_u01 ON &mw_prefix.site_identifiers (si_type, si_key); +CREATE INDEX &mw_prefix.site_identifiers_i01 ON &mw_prefix.site_identifiers (si_site); +CREATE INDEX &mw_prefix.site_identifiers_i02 ON &mw_prefix.site_identifiers (si_key); diff --git a/maintenance/oracle/archives/patch-uploadstash-us_props.sql b/maintenance/oracle/archives/patch-uploadstash-us_props.sql new file mode 100644 index 0000000000..8962dc7c5f --- /dev/null +++ b/maintenance/oracle/archives/patch-uploadstash-us_props.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.uploadstash ADD us_props BLOB; + diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index f28c61fe94..c3c3df9ae1 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -361,7 +361,8 @@ CREATE TABLE &mw_prefix.filearchive ( fa_user NUMBER DEFAULT 0 NOT NULL, fa_user_text VARCHAR2(255) NOT NULL, fa_timestamp TIMESTAMP(6) WITH TIME ZONE, - fa_deleted NUMBER DEFAULT 0 NOT NULL + fa_deleted NUMBER DEFAULT 0 NOT NULL, + fa_sha1 VARCHAR2(32) ); ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_pk PRIMARY KEY (fa_id); ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk1 FOREIGN KEY (fa_deleted_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; @@ -370,6 +371,7 @@ CREATE INDEX &mw_prefix.filearchive_i01 ON &mw_prefix.filearchive (fa_name, fa_t CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_group, fa_storage_key); CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_timestamp); CREATE INDEX &mw_prefix.filearchive_i04 ON &mw_prefix.filearchive (fa_user_text,fa_timestamp); +CREATE INDEX &mw_prefix.filearchive_i05 ON &mw_prefix.filearchive (fa_sha1); CREATE SEQUENCE uploadstash_us_id_seq; CREATE TABLE &mw_prefix.uploadstash ( @@ -388,7 +390,8 @@ CREATE TABLE &mw_prefix.uploadstash ( us_media_type VARCHAR2(32) DEFAULT NULL, us_image_width NUMBER, us_image_height NUMBER, - us_image_bits NUMBER + us_image_bits NUMBER, + us_props BLOB ); ALTER TABLE &mw_prefix.uploadstash ADD CONSTRAINT &mw_prefix.uploadstash_pk PRIMARY KEY (us_id); ALTER TABLE &mw_prefix.uploadstash ADD CONSTRAINT &mw_prefix.uploadstash_fk1 FOREIGN KEY (us_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; @@ -527,10 +530,18 @@ CREATE TABLE &mw_prefix.job ( job_title VARCHAR2(255) NOT NULL, job_timestamp TIMESTAMP(6) WITH TIME ZONE NULL, job_params CLOB NOT NULL + job_random NUMBER NOT NULL default 0, + job_token VARCHAR2(32), + job_token_timestamp TIMESTAMP(6) WITH TIME ZONE, + job_sha1 VARCHAR2(32), + job_attempts NUMBER NOT NULL default 0 ); ALTER TABLE &mw_prefix.job ADD CONSTRAINT &mw_prefix.job_pk PRIMARY KEY (job_id); CREATE INDEX &mw_prefix.job_i01 ON &mw_prefix.job (job_cmd, job_namespace, job_title); CREATE INDEX &mw_prefix.job_i02 ON &mw_prefix.job (job_timestamp); +CREATE INDEX &mw_prefix.job_i03 ON &mw_prefix.job (job_sha1); +CREATE INDEX &mw_prefix.job_i04 ON &mw_prefix.job (job_cmd,job_token,job_random); +CREATE INDEX &mw_prefix.job_i05 ON &mw_prefix.job (job_attempts); CREATE TABLE &mw_prefix.querycache_info ( qci_type VARCHAR2(32) NOT NULL, @@ -670,6 +681,39 @@ CREATE TABLE &mw_prefix.module_deps ( ); CREATE UNIQUE INDEX &mw_prefix.module_deps_u01 ON &mw_prefix.module_deps (md_module, md_skin); +CREATE SEQUENCE sites_site_id_seq MINVALUE 0 START WITH 0; +CREATE TABLE &mw_prefix.sites ( + site_id NUMBER NOT NULL, + site_global_key VARCHAR2(32) NOT NULL, + site_type VARCHAR2(32) NOT NULL, + site_group VARCHAR2(32) NOT NULL, + site_source VARCHAR2(32) NOT NULL, + site_language VARCHAR2(32) NOT NULL, + site_protocol VARCHAR2(32) NOT NULL, + site_domain VARCHAR2(255) NOT NULL, + site_data BLOB NOT NULL, + site_forward NUMBER(1) NOT NULL, + site_config BLOB NOT NULL +); +ALTER TABLE &mw_prefix.sites ADD CONSTRAINT &mw_prefix.sites_pk PRIMARY KEY (site_id); +CREATE UNIQUE INDEX &mw_prefix.sites_u01 ON &mw_prefix.sites (site_global_key); +CREATE INDEX &mw_prefix.sites_i01 ON &mw_prefix.sites (site_type); +CREATE INDEX &mw_prefix.sites_i02 ON &mw_prefix.sites (site_group); +CREATE INDEX &mw_prefix.sites_i03 ON &mw_prefix.sites (site_source); +CREATE INDEX &mw_prefix.sites_i04 ON &mw_prefix.sites (site_language); +CREATE INDEX &mw_prefix.sites_i05 ON &mw_prefix.sites (site_protocol); +CREATE INDEX &mw_prefix.sites_i06 ON &mw_prefix.sites (site_domain); +CREATE INDEX &mw_prefix.sites_i07 ON &mw_prefix.sites (site_forward); + +CREATE TABLE &mw_prefix.site_identifiers ( + si_site NUMBER NOT NULL, + si_type VARCHAR2(32) NOT NULL, + si_key VARCHAR2(32) NOT NULL +); +CREATE UNIQUE INDEX &mw_prefix.site_identifiers_u01 ON &mw_prefix.sites (si_type, si_key); +CREATE INDEX &mw_prefix.site_identifiers_i01 ON &mw_prefix.site_identifiers (si_site); +CREATE INDEX &mw_prefix.site_identifiers_i02 ON &mw_prefix.site_identifiers (si_key); + -- do not prefix this table as it breaks parserTests CREATE TABLE wiki_field_info_full ( table_name VARCHAR2(35) NOT NULL, -- 2.20.1